#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:robot
@file: use_excel.py 
@version:
@time: 2022/03/18 
"""
"""
Excel中的三大对象
workBook
Sheet
Cell

openpyxl对Excel的操作
- 创建一个工作簿 wb = openpyxl.Workbook()
- 新增一个sheet表单 wb.create_sheet("")
- 保存case.xlsx文件：wb.save("")
- 打开工作薄：wb.openpyxl.load_workbook('')
- 选取表单sh = wb["sheet"]
- 读取第一行、第一列的数据：ce = sh.cell(row=1, column=1)
- 按行读取数据： row_data = list(sh.rows)
- 按列读取数据： row_data = list(sh.columns)
- 写入第一行、第四列的数据 sh.cell(row=1,column=4,value='result')
- 获取最大行总数，最大列总数，sh.max_row、sh.max_column
- del 删除表单的用法： del wb[".."]
    - sh = wb[""]
    - wb.remove(sh)
- 关闭工作薄 wb.close()

"""
import openpyxl
from openpyxl.styles import PatternFill


class UtilExcel:

    def __init__(self, path):
        self.wb = openpyxl.Workbook()
        self.path = path

    def update_path(self, path):
        self.path = path
        self.wb = openpyxl.Workbook()

    def create_sheet(self, sheet_name):
        self.wb.create_sheet(sheet_name)
        self.wb[sheet_name].column_dimensions['A'].width = 30
        self.wb[sheet_name].column_dimensions['B'].width = 30
        self.wb[sheet_name].column_dimensions['C'].width = 30
        self.wb[sheet_name].column_dimensions['D'].width = 30
        self.wb[sheet_name].column_dimensions['E'].width = 30
        self.wb[sheet_name].column_dimensions['F'].width = 30
        self.wb[sheet_name].column_dimensions['G'].width = 30
        self.wb[sheet_name].column_dimensions['H'].width = 30
        self.wb[sheet_name].column_dimensions['I'].width = 30
        self.wb.save(self.path)

    def write_data(self, sheet, row, column, value):
        self.wb[sheet].cell(row=row, column=column, value=value)
        self.wb.save(self.path)

    def set_cell_style(self, sheet, row, column, find_str, style=None):
        _style = {
            "patternType": 'solid',
            "fgColor": 'F562a4',
            "bgColor": '0000ff'
        }
        if style:
            _style.update(style)
        error = PatternFill(
            **_style
        )
        get_str = self.wb[sheet].cell(row, column).value
        if find_str in get_str:
            self.wb[sheet].cell(row, column).fill = error
        self.wb.save(self.path)

    def read_cell_data(self, sheet, row, column):
        return self.wb[sheet].cell(row, column).value

    def save(self):
        self.wb.save(self.path)

    def load(self):
        self.wb = openpyxl.load_workbook(self.path)

    def __del__(self):
        self.wb.close()
